
create table #ZZMD00(
	ShiptoNumber	INTEGER,
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	ItemNumberID	DECIMAL(19, 0),
	InvoiceDateID	DECIMAL(19, 0),
	GLDateID	DECIMAL(19, 0),
	CustomerNumberID	DECIMAL(19, 0),
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT,
	WJXBFS3	FLOAT)

insert into #ZZMD00
select	a11.ShiptoNumber  ShiptoNumber,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ItemNumberID  ItemNumberID,
	a11.InvoiceDateID  InvoiceDateID,
	a11.GLDateID  GLDateID,
	a11.CustomerNumberID  CustomerNumberID,
	sum(a11.ExtendedPrice)  WJXBFS1,
	sum(a11.QuantityShipped)  WJXBFS2,
	sum(a11.ExtendedCost)  WJXBFS3
from	Sales_Star_Facts	a11
	join	Sales_Star_FactsExt	a12
	  on 	(a11.FactsExtID = a12.FactsExtID)
	join	Common_Star_Item	a13
	  on 	(a11.ItemNumberID = a13.ItemNumberID)
	join	Sales_Star_Customer	a14
	  on 	(a11.CustomerNumberID = a14.CustomerNumberID)
	join	Common_Star_Dates	a15
	  on 	(a11.GLDateID = a15.CompanyDateID)
where	(a12.TransactionType in (2)
 and a14.ShiptoSearchTypeCode in ('CC ', 'CCS', 'CCX', 'CE ', 'CF ', 'CFD', 'CFP', 'CFS', 'CFX')
 and a12.OrderTypeCode in ('CO', 'SD', 'SO', 'SU')
 and a11.ShiptoNumber <> 6569584
 and a15.Dates between '2012-04-02' and '2012-04-15'
 and a13.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE', 'Adv Pricing Adj Line'))
group by	a11.ShiptoNumber,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ItemNumberID,
	a11.InvoiceDateID,
	a11.GLDateID,
	a11.CustomerNumberID OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD01(
	ShiptoNumber	INTEGER,
	ShiptoNumber0	INTEGER,
	ShiptoNumber1	INTEGER,
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	ItemNumberID	DECIMAL(19, 0),
	WJXBFS1	FLOAT)

insert into #ZZMD01
select	a11.ShiptoNumber  ShiptoNumber,
	a13.ShiptoNumber  ShiptoNumber0,
	a13.ShiptoNumber  ShiptoNumber1,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ItemNumberID  ItemNumberID,
	sum(a11.PL_UnitPrice)  WJXBFS1
from	Sales_Star_Price_Facts	a11
	join	Sales_Star_PriceLedger	a12
	  on 	(a11.LineNumber = a12.LineNumber and
	a11.OrderNumber = a12.OrderNumber and
	a11.OrderNumberDocumentCompany = a12.OrderNumberDocumentCompany and
	a11.OrderTypeCode = a12.OrderTypeCode and
	a11.PriceAdjustmentKeyID = a12.PriceAdjustmentKeyID and
	a11.SourceID = a12.SourceID)
	join	Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
	join	Common_Star_Item	a14
	  on 	(a11.ItemNumberID = a14.ItemNumberID)
where	(a13.ShiptoSearchTypeCode in ('CC ', 'CCS', 'CCX', 'CE ', 'CF ', 'CFD', 'CFP', 'CFS', 'CFX')
 and a11.ShiptoNumber <> 6569584
 and a14.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE', 'Adv Pricing Adj Line')
 and a12.SequenceNumber between 902 and 998)
group by	a11.ShiptoNumber,
	a13.ShiptoNumber,
	a13.ShiptoNumber,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ItemNumberID OPTION (MERGE JOIN, LOOP JOIN)

select	distinct pa11.ShiptoNumber  ShiptoNumber,
	pa11.OrderNumber  OrderNumber,
	pa11.LineNumber  LineNumber,
	a12.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	pa11.InvoiceDateID  InvoiceDateID,
	a16.Dates  Dates,
	pa11.GLDateID  GLDateID,
	a15.Dates  Dates0,
	a14.ItemNumber02  ItemNumber02,
	a14.Description01  Description01,
	a14.Description01  Description010,
	pa11.ItemNumberID  ItemNumberID,
	pa11.CustomerNumberID  CustomerNumberID,
	LEFT(a12.CustomerNumberID, (LEN(a12.CustomerNumberID) - 10))  CustCol_4,
	pa11.WJXBFS1  WJXBFS1,
	(ISNULL(pa11.WJXBFS1, 0) + ISNULL((pa13.WJXBFS1 * pa11.WJXBFS2), 0))  NetSales,
	ISNULL((pa13.WJXBFS1 * pa11.WJXBFS2), 0)  WJXBFS2,
	pa11.WJXBFS3  WJXBFS3,
	pa11.WJXBFS2  WJXBFS4
from	#ZZMD00	pa11
	join	Sales_Star_Customer	a12
	  on 	(pa11.CustomerNumberID = a12.CustomerNumberID)
	left outer join	#ZZMD01	pa13
	  on 	(a12.ShiptoNumber = pa13.ShiptoNumber0 and
	a12.ShiptoNumber = pa13.ShiptoNumber1 and
	pa11.ItemNumberID = pa13.ItemNumberID and
	pa11.LineNumber = pa13.LineNumber and
	pa11.OrderNumber = pa13.OrderNumber and
	pa11.ShiptoNumber = pa13.ShiptoNumber)
	join	Common_Star_Item	a14
	  on 	(pa11.ItemNumberID = a14.ItemNumberID)
	join	Common_Star_Dates	a15
	  on 	(pa11.GLDateID = a15.CompanyDateID)
	join	Common_Star_Dates	a16
	  on 	(pa11.InvoiceDateID = a16.CompanyDateID)


drop table #ZZMD00

drop table #ZZMD01

